#!/bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto
MYSQL_HOME=/usr/bin/mysql

start_time=`date +"%Y-%m-%d %H:%M:%S"`
echo "sqoop export start ${start_time}" >> ./sqoop_liujie.log

echo '========================================'
echo '================开始导出================='
echo '========================================'

${MYSQL_HOME} -h192.168.88.80 -p3306 -uroot -p123456 -e "
CREATE DATABASE IF NOT EXISTS online_edu DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
use online_edu;
drop table IF EXISTS online_edu.rpt_stu_attendance;

create table if not exists online_edu.rpt_stu_attendance
(
    class_id                   INT comment '班级id',
    studying_student_count     INT comment '在读班级人数',
    morning_attendance_count   BIGINT comment '上午正常出勤人数',
    morning_leave_count        BIGINT comment '上午请假人数',
    morning_late_count         BIGINT comment '上午迟到人数',
    morning_truant_count       BIGINT comment '上午旷课人数',
    afternoon_attendance_count BIGINT comment '下午正常出勤人数',
    afternoon_leave_count      BIGINT comment '下午请假人数',
    afternoon_late_count       BIGINT comment '下午迟到人数',
    afternoon_truant_count     BIGINT comment '下午旷课人数',
    evening_attendance_count   BIGINT comment '晚上正常出勤人数',
    evening_leave_count        BIGINT comment '晚上请假人数',
    evening_late_count         BIGINT comment '晚上迟到人数',
    evening_truant_count       BIGINT comment '晚上旷课人数',
    morning_attendance_ratio   decimal(10, 2) comment '上午出勤率',
    morning_late_ratio         decimal(10, 2) comment '上午迟到率',
    morning_leave_ratio        decimal(10, 2) comment '上午请假率',
    morning_truant_ratio       decimal(10, 2) comment '上午旷课率',
    afternoon_attendance_ratio decimal(10, 2) comment '下午出勤率',
    afternoon_late_ratio       decimal(10, 2) comment '下午迟到率',
    afternoon_leave_ratio      decimal(10, 2) comment '下午请假率',
    afternoon_truant_ratio     decimal(10, 2) comment '下午旷课率',
    evening_attendance_ratio   decimal(10, 2) comment '晚上出勤率',
    evening_late_ratio         decimal(10, 2) comment '晚上迟到率',
    evening_leave_ratio        decimal(10, 2) comment '晚上请假率',
    evening_truant_ratio       decimal(10, 2) comment '晚上旷课率',
    studying_date              LONGTEXT      comment '上课日期'
);
"

wait

${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into mysql.online_edu.rpt_stu_attendance
select class_id,
       studying_student_count,
       morning_attendance_count,
       morning_leave_count,
       morning_late_count,
       morning_truant_count,
       afternoon_attendance_count,
       afternoon_leave_count,
       afternoon_late_count,
       afternoon_truant_count,
       evening_attendance_count,
       evening_leave_count,
       evening_late_count,
       evening_truant_count,
       morning_attendance_ratio,
       morning_late_ratio,
       morning_leave_ratio,
       morning_truant_ratio,
       afternoon_attendance_ratio,
       afternoon_late_ratio,
       afternoon_leave_ratio,
       afternoon_truant_ratio,
       evening_attendance_ratio,
       evening_late_ratio,
       evening_leave_ratio,
       evening_truant_ratio,
       studying_date
from hive.edu_rpt.rpt_stu_attendance;
"
start_time=`date +"%Y-%m-%d %H:%M:%S"`
echo "sqoop export end ${start_time}" >> ./sqoop_liujie.log

echo '========================================'
echo '=================success================'
echo '========================================'






